--隐私面单汇总
--开发 侯文龙
--时间 ：2022-07-11
insert overwrite table jms_dm.dm_privacy_waybill_sum_dt partition (dt, type1)
--隐私面单汇总
select max(subordinate_agent_code)                                                                                  as subordinate_agent_code     --所属代理区code
     , max(subordinate_agent_name)                                                                                  as subordinate_agent_name     --所属代理区
     , max(first_franchisee_code)                                                                                   as first_franchisee_code      --加盟商code
     , max(first_franchisee_name)                                                                                   as first_franchisee_name      --加盟商
     , code                                                                                                                                       --网点编码
     , max(name)                                                                                                    as name                       --网点名字
     , sum(if(is_privacy = 1, 1, 0))                                                                                as waybill_cnt                --到件、发件 运单总数
     , sum(if(is_privacy = 1 and ordersource_code = 'D12', 1, 0))                                                   as gw_cnt                     --官网票数
     , sum(if(is_privacy = 1 and ordersource_code = 'D08', 1, 0))                                                   as wx_cnt                     --微信票数
     , sum(if(is_privacy = 1 and ordersource_code = 'D02', 1, 0))                                                   as vip_cnt                    --VIP系统
     , sum(if(is_privacy = 1 and (ordersource_code = 'D12' or ordersource_code = 'D08' or ordersource_code = 'D02' or
                                  ordersource_code = 'D13' or ordersource_code = 'D14' or ordersource_code = 'D61'), 1,
              0))                                                                                                   as zypt_cnt                   --自有平台票数
     , sum(if(is_privacy = 1 and ordersource_code = 'D09', 1, 0))                                                   as thd_cnt                    --桃花岛
     , sum(if(is_privacy = 1 and ordersource_code = 'D67', 1, 0))                                                   as zjs_cnt                    --紫金山
--    ,sum(if(ordersource_code='D63',1,0)) as yj_cnt
     , sum(if(is_privacy = 1 and ordersource_code = 'D190', 1, 0))                                                  as xyf_cnt                    --逍遥峰
     , sum(if(is_privacy = 1 AND ordersource_code = 'D801', 1, 0))                                                  as qxt_cnt                    --七星潭
     , sum(if(is_privacy = 1 and (ordersource_code = 'D801' or ordersource_code = 'D09' or ordersource_code = 'D67' or
                                  ordersource_code = 'D190'), 1,
              0))                                                                                                   as pt_cnt                     --平台
     , sum(if(is_privacy = 1 and ordersource_code != 'D801' and ordersource_code != 'D09' and
              ordersource_code != 'D67' and ordersource_code != 'D190'
                  and ordersource_code != 'D12' and ordersource_code != 'D08' and ordersource_code != 'D02'
                  and ordersource_code != 'D13' and ordersource_code != 'D14' and ordersource_code != 'D61', 1,
              0))                                                                                                   as qt_cnt
     , sum(if(is_privacy = 1 and (dt = to_date(sign_later_scantime) or dt = to_date(out_store_scan_time) or
                                  dt = to_date(instore_scan_time)), 1,
              0))                                                                                                   as sign_sum--签收量
     , sum(if(is_privacy = 1 and dt = to_date(out_store_scan_time), 1, 0))                                          as out_store_sum--出库量
     , sum(if(is_privacy = 1 and dt = to_date(instore_scan_time), 1, 0))                                            as instore_sum                --入库量
     , dt                                                                                                           as date_time                  --业务日期
     , type1                                                                                                        as tpye2                      --类型
     , sum(if(is_privacy = 1 and ordersource_code = 'D13', 1, 0))                                                   as network_ewm_sum            --网点二维码
     , sum(if(is_privacy = 1 and ordersource_code = 'D14', 1, 0))                                                   as ywy_ewm_sum                --业务员二维码
     , sum(if(is_privacy = 1 and ordersource_code = 'D61', 1, 0))                                                   as zz_ewm_sum                 --纸质二维码
     , sum(if(ordersource_code = 'D67', 1, 0))                                                                      as zjs_bill_count             --紫金山平台业务量
     , sum(if(ordersource_code = 'D190', 1, 0))                                                                     as xyf_bill_count             --逍遥峰平台业务量
     , sum(if(ordersource_code = 'D801', 1, 0))                                                                     as qxt_bill_count             --七星潭平台业务量
     , sum(if(ordersource_code = 'D619', 1, 0))                                                                     as jdw_bill_count             --极地湾平台业务量
     , sum(if(ordersource_code = 'D920', 1, 0))                                                                     as wph_bill_count             --唯品会平台业务量
     , sum(if(ordersource_code = 'D501', 1, 0))                                                                     as sn_bill_count              --苏宁平台业务量
     , sum(if(ordersource_code = 'D09', 1, 0))                                                                      as thd_bill_count             --桃花岛业务量
     , sum(if(
        (ordersource_code = 'D12' or ordersource_code = 'D08' or ordersource_code = 'D02' or ordersource_code = 'D13' or
         ordersource_code = 'D14' or ordersource_code = 'D61'), 1,
        0))                                                                                                         as waybill_sum                --自有总业务量
     , sum(if(ordersource_code = 'D801' and is_ali_order = 1, 1, 0))                                                as ali_qxt_bill_count         --淘系七星潭平台业务量
     , sum(
        if(ordersource_code = 'D801' and is_ali_order = 1 and is_privacy = 1, 1, 0))                                as ali_qxt_privacy_bill_count --淘系七星潭隐私面单量量
     , dt
     , type1
from jms_dm.dm_privacy_waybill_detail_dt
where dt > date_add('{{ execution_date | cst_ds }}', -10)
  and dt <= '{{ execution_date | cst_ds }}'
group by dt, type1, code
    distribute by dt, abs(hash(code)) % 1
;
